package com.bfg.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@SuppressWarnings("serial")
public class TestDBServlet extends HttpServlet {

	private Connection conn;
	private Statement stmt;
	private ResultSet rs;

	// properties for connecting to database
	String url = "jdbc:mysql://localhost:3306/bfg";
	String username = "root";
	String password = "@";

	@Override
	public void init() throws ServletException {
		// init var
	}

	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		resp.setContentType("text/html");
		PrintWriter out = resp.getWriter();
		out.println("<html>");
	    out.println("<head><title>Servlet JDBC</title></head>");
	    out.println("<body>");
	    out.println("<h1>Servlet JDBC</h1>");
		// connecting to database
		conn = null;
		stmt = null;
		rs = null;
		try {
			// loading driver
			Class.forName("com.mysql.jdbc.Driver");
			// create connection
			conn = DriverManager.getConnection(url, username, password);
			// create query
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM PRODUCT LIMIT 5");
			while (rs.next()) {
				out.print(rs.getString("ISBN"));
				out.print("\t\t\t");
				out.print(rs.getString("TITLE"));
				out.print("\t\t\t");
				out.print(rs.getObject("PRICE").toString());
				out.print("\t\t\t");
				out.print(rs.getObject("PUB_DATE").toString());
				out.print("<br>");
			}

		} catch (SQLException e) {
			throw new ServletException("Servlet could not display records", e);
		}
		catch (ClassNotFoundException e) {
			throw new ServletException("JDBC Driver not found", e);
		} finally {
			//close connection if exist
			try {
				if(rs!=null) {
					rs.close();
					rs=null;
				}
				if(stmt!=null) { 
					stmt.close();
					stmt=null;
				}
				if(conn!=null) {
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {}
		}
	    out.println("</body></html>");
	}

	@Override
	public void destroy() {
		// clean var
	}
}
